/*******************************************************************************
Name: create_pop_working
*******************************************************************************/


clear

capture log close

log using  "${logdir}1-create_pop_working.log", replace

/*------------------------------------------------------------*/
/*------------------------------------------------------------*/
*READ IN CTPP DATA (1990, 2000)
/*------------------------------------------------------------*/
/*------------------------------------------------------------*/

*note: cityfips is not actually place fips code; it is place census code

/*------------------------------------------------------------*/
*YEAR: 2000
/*------------------------------------------------------------*/

*COUNTY DATA

insheet using "${rawdir}ctpp_county_2000/132360852_P2-004.csv"

keep state state_nm county tab4x1-tab4x15

rename tab4x1 emp_tot

forvalues i = 2(1)15 {
rename tab4x`i' temp_emp_ind`i'
}

gen emp_ind1 = temp_emp_ind2
gen emp_ind2 = temp_emp_ind3
gen emp_ind3 = temp_emp_ind4
gen emp_ind4 = temp_emp_ind5
gen emp_ind5 = temp_emp_ind6
gen emp_ind6 = temp_emp_ind9
gen emp_ind7 = temp_emp_ind14
gen emp_ind8 = temp_emp_ind15
gen emp_ind9 = temp_emp_ind11
gen emp_ind10 = temp_emp_ind8 + temp_emp_ind10 + temp_emp_ind12 + temp_emp_ind13 + temp_emp_ind7

drop temp_emp_*

reshape long emp_ind, i(state county) j(industry)

rename state statefips
rename county countyfips

replace countyfips = 25 if statefips == 12 & countyfips == 86

gen year = 2000

merge statefips countyfips using "${rawdir}70msa_necma_cw.dta", sort uniqusing keep(msafips name msa_name)

drop _merge

save "${datadir}ctpp_county_00.dta", replace
clear

*CITY DATA

insheet using "${rawdir}ctpp_place_2000/132360852_P2-004.csv"

keep state state_nm place placece tab4x1-tab4x15

rename tab4x1 emp_tot

forvalues i = 2(1)15 {
rename tab4x`i' temp_emp_ind`i'
}

gen emp_ind1 = temp_emp_ind2
gen emp_ind2 = temp_emp_ind3
gen emp_ind3 = temp_emp_ind4
gen emp_ind4 = temp_emp_ind5
gen emp_ind5 = temp_emp_ind6
gen emp_ind6 = temp_emp_ind9
gen emp_ind7 = temp_emp_ind14
gen emp_ind8 = temp_emp_ind15
gen emp_ind9 = temp_emp_ind11
gen emp_ind10 = temp_emp_ind8 + temp_emp_ind10 + temp_emp_ind12 + temp_emp_ind13 + temp_emp_ind7

drop temp_emp_*


reshape long emp_ind, i(state place) j(industry)

rename placece cityfips
rename state statefips

replace cityfips = 305 if cityfips == 327 & statefips == 4 /*Scottsdale, AZ*/
replace cityfips = 360 if cityfips == 368 & statefips == 4 /*Tempe, AZ*/
replace cityfips = 380 if cityfips == 385 & statefips == 4 /*Tucson, AZ*/

replace cityfips = 2910 if cityfips == 2912 & statefips == 6 /*Torrance, CA*/
replace cityfips = 3000 if cityfips == 2984 & statefips == 6 /*Vallejo, CA*/

replace cityfips = 1001 if cityfips == 1000 & statefips == 12 /*???*/
replace cityfips = 1000 if cityfips == 1003 & statefips == 12 /*Jacksonville, FL*/

replace cityfips = 165 if cityfips == 166 & statefips == 13 /*Augusta, GA*/

replace cityfips = 4350 if cityfips == 4347 & statefips == 48 /*Tyler, TX*/
replace cityfips = 2400 if cityfips == 2389 & statefips == 48 /*Laredo, TX*/
replace cityfips = 2810 if cityfips == 2807 & statefips == 48 /*Midland, TX*/

replace cityfips = 1350 if cityfips == 1347 & statefips == 53 /*Vancouver, WA*/

gen year = 2000

merge statefips cityfips using "${rawdir}cc_to_msa.dta", sort uniqusing

drop _merge place

save "${datadir}ctpp_city_00.dta", replace
clear

/*------------------------------------------------------------*/
*YEAR: 1990
/*------------------------------------------------------------*/

*COUNTY DATA
insheet using "${rawdir}ctpp_county_1990/132360852_SB03.csv"

keep countyw statew_nm statew_fips countyw_nm sb03_0101-sb03_0119

rename countyw county
rename statew_nm state_nm
rename statew_fips statefips
rename countyw_nm county_nm

rename sb03_0101 emp_tot

forvalues i = 102(1)119 {
rename sb03_0`i' temp_emp_ind`i'
}

gen emp_ind1 = temp_emp_ind102 + temp_emp_ind103
gen emp_ind2 = temp_emp_ind104
gen emp_ind3 = temp_emp_ind105 + temp_emp_ind106
gen emp_ind4 = temp_emp_ind109
gen emp_ind5 = temp_emp_ind110
gen emp_ind6 = temp_emp_ind111
gen emp_ind7 = temp_emp_ind118
gen emp_ind8 = temp_emp_ind119
gen emp_ind9 = temp_emp_ind115 + temp_emp_ind116
gen emp_ind10 = temp_emp_ind107 + temp_emp_ind108 + temp_emp_ind112 + temp_emp_ind113 + temp_emp_ind114 + temp_emp_ind117

drop temp_emp_*


reshape long emp_ind, i(statefips county) j(industry)

rename county countyfips

replace countyfips = 25 if statefips == 12 & countyfips == 86

gen year = 1990

merge statefips countyfips using "${rawdir}70msa_necma_cw.dta", sort uniqusing keep(msafips name msa_name)

drop _merge

save "${datadir}ctpp_county_90.dta", replace
clear

*CITY DATA
insheet using "${rawdir}ctpp_place_1990/132360852_SB03.csv"
keep placew statew_nm statew_fips sb03_0101-sb03_0119

rename placew placece
rename statew_nm state_nm
rename statew_fips statefips

rename sb03_0101 emp_tot


forvalues i = 102(1)119 {
rename sb03_0`i' temp_emp_ind`i'
}

gen emp_ind1 = temp_emp_ind102 + temp_emp_ind103
gen emp_ind2 = temp_emp_ind104
gen emp_ind3 = temp_emp_ind105 + temp_emp_ind106
gen emp_ind4 = temp_emp_ind109
gen emp_ind5 = temp_emp_ind110
gen emp_ind6 = temp_emp_ind111
gen emp_ind7 = temp_emp_ind118
gen emp_ind8 = temp_emp_ind119
gen emp_ind9 = temp_emp_ind115 + temp_emp_ind116
gen emp_ind10 = temp_emp_ind107 + temp_emp_ind108 + temp_emp_ind112 + temp_emp_ind113 + temp_emp_ind114 + temp_emp_ind117

drop temp_emp_*


reshape long emp_ind, i(statefips placece) j(industry)

rename placece cityfips

replace cityfips = 1000 if cityfips == 1003 & statefips == 12 /*Jacksonville, FL*/

gen year = 1990

merge statefips cityfips using "${rawdir}cc_to_msa.dta", sort uniqusing

drop _merge

save "${datadir}ctpp_city_90.dta", replace

/*------------------------------------------------------------*/
/*------------------------------------------------------------*/
*COMBINE CTPP FILES
/*------------------------------------------------------------*/
/*------------------------------------------------------------*/

use "${datadir}ctpp_county_90.dta"
append using "${datadir}ctpp_county_00.dta"

sort statefips countyfips year ind

compress

save "${datadir}ctpp_county_working.dta", replace


use "${datadir}ctpp_city_90.dta"
append using "${datadir}ctpp_city_00.dta"

sort statefips cityfips year ind

keep if name != ""

compress

save "${datadir}ctpp_city_working.dta", replace


use "${datadir}ctpp_city_working.dta", clear

egen emp_tot_cc = sum(emp_tot), by(msafips industry year)
egen emp_ind_cc = sum(emp_ind), by(msafips industry year)

drop if msafips == .

collapse (mean) emp_tot_cc emp_ind_cc, by(msafips industry year)

tempfile ccwork
save `ccwork'

use "${datadir}ctpp_county_working.dta", clear

egen emp_tot_msa = sum(emp_tot), by(msafips industry year)
egen emp_ind_msa = sum(emp_ind), by(msafips industry year)

drop if msafips == .

collapse (mean) emp_tot_msa emp_ind_msa, by(msafips msa_name industry year)

merge msafips industry year using `ccwork', sort
keep if _merge == 3
drop _merge


*collapse from msafips to metarea

merge msafips using "${rawdir}msafips_metarea.dta", sort uniqusing

keep if _merge == 3
drop _merge

replace msa_name = "Dallas-Forth Worth, TX MSA" if metarea == 192
replace msa_name = "Raleigh-Durham, NC MSA" if metarea == 664
replace msa_name = "Salt Lake City-Ogden, UT MSA" if metarea == 716
replace msa_name = "Chicago-Gary-Lake, IL-INVA  MSA" if metarea == 160

replace msa_name = "Los Angeles-Long Beach, CA MSA" if metarea == 448
replace msa_name = "Norfolk-VA Beach-Newport News, VA MSA" if metarea == 572
replace msa_name = "Richmond-Petersburg, VA MSA" if metarea == 676
replace msa_name = "San Francisco-Oakland-Vallejo, CA MSA" if metarea == 736
replace msa_name = "New York-Northeastern, NY-NJ MSA" if metarea == 560

collapse (sum) emp_tot_msa emp_ind_msa emp_tot_cc emp_ind_cc, by(metarea msa_name industry year)

save "${datadir}pop_working.dta", replace

/*------------------------------------------------------------*/
/*------------------------------------------------------------*/
*READ IN IPUMS DATA (1970, 1980)
/*------------------------------------------------------------*/
/*------------------------------------------------------------*/

/*------------------------------------------------------------*/
*YEAR: 1970
/*------------------------------------------------------------*/

use "${rawdir}ipums_1970.dta", clear

drop if metarea == 0
keep if empstat == 1

keep if (pwtype == 2 | pwtype == 3 | pwtype == 4 | pwtype == 7| pwtype == 8)

gen ccwork = (pwtype == 2 | pwtype == 3)

egen emp_tot_cc = sum(perwt*ccwork), by(metarea)
egen emp_tot_msa = sum(perwt), by(metarea)

gen ind_cat = 0
replace ind_cat = 1 if ind1990 >= 10 & ind1990 <= 32
replace ind_cat = 2 if ind1990 >= 40 & ind1990 <= 50
replace ind_cat = 3 if ind1990 >= 60 & ind1990 <= 60
replace ind_cat = 4 if ind1990 >= 100 & ind1990 <= 222
replace ind_cat = 5 if ind1990 >= 230 & ind1990 <= 392
replace ind_cat = 6 if ind1990 >= 400 & ind1990 <= 472
replace ind_cat = 7 if ind1990 >= 500 & ind1990 <= 571
replace ind_cat = 8 if ind1990 >= 580 & ind1990 <= 691
replace ind_cat = 9 if ind1990 >= 700 & ind1990 <= 712
replace ind_cat = 10 if ind1990 >= 721 & ind1990 <= 760
replace ind_cat = 11 if ind1990 >= 761 & ind1990 <= 810
replace ind_cat = 12 if ind1990 >= 812 & ind1990 <= 840
replace ind_cat = 13 if ind1990 >= 842 & ind1990 <= 860
replace ind_cat = 14 if ind1990 >= 812 & ind1990 <= 893 & ind_cat == 0
replace ind_cat = 15 if ind1990 >= 900 & ind1990 <= 932

tab ind_cat
drop if ind_cat == 0

egen emp_ind_cc = sum(perwt*ccwork), by(metarea ind_cat)
egen emp_ind_msa = sum(perwt), by(metarea ind_cat)


collapse (mean) emp_tot_cc emp_ind_cc emp_tot_msa emp_ind_msa, by(metarea ind_cat year)

save "${datadir}pop_working70.dta", replace

/*------------------------------------------------------------*/
*YEAR: 1980
/*------------------------------------------------------------*/

use "${rawdir}ipums_1980.dta", clear

drop if metarea == 0
keep if empstat == 1

keep if (pwtype == 2 | pwtype == 3 | pwtype == 4 | pwtype == 7| pwtype == 8)

gen ccwork = (pwtype == 2 | pwtype == 3)

egen emp_tot_cc = sum(2*perwt*ccwork), by(metarea)
egen emp_tot_msa = sum(2*perwt), by(metarea)

gen ind_cat = 0
replace ind_cat = 1 if ind1990 >= 10 & ind1990 <= 32
replace ind_cat = 2 if ind1990 >= 40 & ind1990 <= 50
replace ind_cat = 3 if ind1990 >= 60 & ind1990 <= 60
replace ind_cat = 4 if ind1990 >= 100 & ind1990 <= 222
replace ind_cat = 5 if ind1990 >= 230 & ind1990 <= 392
replace ind_cat = 6 if ind1990 >= 400 & ind1990 <= 472
replace ind_cat = 7 if ind1990 >= 500 & ind1990 <= 571
replace ind_cat = 8 if ind1990 >= 580 & ind1990 <= 691
replace ind_cat = 9 if ind1990 >= 700 & ind1990 <= 712
replace ind_cat = 10 if ind1990 >= 721 & ind1990 <= 760
replace ind_cat = 11 if ind1990 >= 761 & ind1990 <= 810
replace ind_cat = 12 if ind1990 >= 812 & ind1990 <= 840
replace ind_cat = 13 if ind1990 >= 842 & ind1990 <= 860
replace ind_cat = 14 if ind1990 >= 812 & ind1990 <= 893 & ind_cat == 0
replace ind_cat = 15 if ind1990 >= 900 & ind1990 <= 932

tab ind_cat
drop if ind_cat == 0

egen emp_ind_cc = sum(2*perwt*ccwork), by(metarea ind_cat)
egen emp_ind_msa = sum(2*perwt), by(metarea ind_cat)

collapse (mean) emp_tot_cc emp_ind_cc emp_tot_msa emp_ind_msa, by(metarea ind_cat year)

save "${datadir}pop_working80.dta", replace


/*------------------------------------------------------------*/
/*------------------------------------------------------------*/
*COMBINE CTPP AND IPUMS DATA
/*------------------------------------------------------------*/
/*------------------------------------------------------------*/

use "${datadir}pop_working.dta", clear
append using "${datadir}pop_working70.dta"
append using "${datadir}pop_working80.dta"

collapse (mean) emp_tot_msa emp_tot_cc, by(year msa_name metarea)

rename emp_tot_msa emp_tot

sort metarea year

tab year

save "${datadir}pop_working.dta", replace

rm "${datadir}ctpp_city_90.dta"
rm "${datadir}ctpp_city_00.dta"
rm "${datadir}ctpp_city_working.dta"

rm "${datadir}ctpp_county_90.dta"
rm "${datadir}ctpp_county_00.dta"
rm "${datadir}ctpp_county_working.dta"

rm "${datadir}pop_working70.dta"
rm "${datadir}pop_working80.dta"


log close

